import pymysql  
  
class EmployeeDatabase:  
    def __init__(self, host='your_host', user='your_host', password='your_password', db='attendance_system'):  
        self.host = host  
        self.user = user  
        self.password = password  
        self.db = db  
        self.connection = None  
  
    def connect(self):  
        self.connection = pymysql.connect(host=self.host, user=self.user, password=self.password, db=self.db)  
  
    def close(self):  
        if self.connection:  
            self.connection.close()  
  
    def create_table(self):  
        """创建employees表（如果尚不存在）"""  
        with self.connection.cursor() as cursor:  
            sql = '''  
            CREATE TABLE IF NOT EXISTS employees (  
                id INT AUTO_INCREMENT PRIMARY KEY,  
                employee_id VARCHAR(255) NOT NULL UNIQUE,  
                name VARCHAR(255) NOT NULL,  
                department VARCHAR(255) NOT NULL,  
                password VARCHAR(255) NOT NULL,  
                face_data VARCHAR(255)   
            )   
            '''  
            cursor.execute(sql)  
            self.connection.commit()  
            print("Table created successfully.")  
  
    def find_by_id(self, employee_id):  
        """根据工号查找员工信息"""  
        with self.connection.cursor() as cursor:  
            sql = "SELECT * FROM employees WHERE employee_id = %s"  
            cursor.execute(sql, (employee_id,))  
            result = cursor.fetchone()  
            return result  
  
    def traverse_all(self):  
        """遍历所有人的信息"""  
        with self.connection.cursor() as cursor:  
            sql = "SELECT * FROM employees"  
            cursor.execute(sql)  
            results = cursor.fetchall()  
            for row in results:  
                print(row)  
  
    def delete(self, employee_id):  
        """删除指定工号的员工信息"""  
        with self.connection.cursor() as cursor:  
            sql = "DELETE FROM employees WHERE employee_id = %s"  
            cursor.execute(sql, (employee_id,))  
            self.connection.commit()  
            print(f"Employee with ID {employee_id} deleted.")  
  
    def insert(self, employee_id, name, department, password, face_data):  
        """插入新员工信息"""  
        with self.connection.cursor() as cursor:  
            sql = "INSERT INTO employees (employee_id, name, department, password, face_data) VALUES (%s, %s, %s, %s, %s)"  
            cursor.execute(sql, (employee_id, name, department, password, face_data))  
            self.connection.commit()  
            print(f"Employee with ID {employee_id} inserted.")  
  
    def __enter__(self):  
        self.connect()  
        return self  
  
    def __exit__(self, exc_type, exc_val, exc_tb):  
        self.close()  
'''
# 示例  
with EmployeeDatabase() as db:  
    # 创建表（如果尚不存在）  
    db.create_table()  
      
    # 插入数据  
    db.insert('007', '张三', '技术部', '123456', 'face_data_1')  
    #db.insert(2, '李四', '市场部', '654321', 'face_data_2')  
  
    # 查找数据  
    #employee = db.find_by_id('001')  
    #if employee:  
    #    print(employee)  
  
    # 遍历所有数据  
    db.traverse_all()  
  
    # 删除数据  
    db.delete(2)  
  
    # 再次遍历所有数据，确认删除是否成功  
    db.traverse_all()
'''
